﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

public partial class SqlImport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.ContentType = "text/plain";
    }

    public List<Code.DAL.Table> CurrentDables
    {
        get
        {
            return (List<Code.DAL.Table>)Session["Tables"];
        }
    }

    public Code.DAL.Table CurrentTable
    {
        get
        {
            return CurrentDables.Find(x => x.Name == TableName);
        }
    }

    public string TableName
    {
        get
        {
            return Request["TableName"];
        }
    }

    public string Sql
    {
        get
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("if(@TableName='" + TableName + "')");
            sb.AppendLine(" begin");
            sb.AppendLine("     select @maxTime =max(ISNULL(ModTime,RecTime)) from " + TableName);
            sb.AppendLine("     if(@maxTime is null)");
            sb.AppendLine("         set @maxTime = '1970-1-1'");
            sb.AppendLine("     insert into "+TableName+" select");
            sb.Append("     ");
            for (int i = 0; i < CurrentTable.Columns.Count; i++)
            {
                sb.Append("[" + CurrentTable.Columns[i].Name + "]");
                if (i < CurrentTable.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }
            sb.AppendLine();
            sb.AppendLine("     from EMBASE.embase2.dbo." + TableName + " as a where");
            sb.AppendLine("     not exists(select 1 from "+TableName+" where");
            string str = "";
            for (int i = 0; i < CurrentTable.Columns.Count; i++)
            {
                if (CurrentTable.Columns[i].IsKey)
                {
                    str += "[" + CurrentTable.Columns[i].Name + "]=a.[" + CurrentTable.Columns[i].Name + "] and";
                }
            }
            str = str.Substring(0, str.Length - 3);
            sb.AppendLine(str + ")");
            sb.AppendLine("     update "+TableName+" set");
            sb.Append("     ");
            str = "";
            for (int i = 0; i < CurrentTable.Columns.Count; i++)
            {
                if (!CurrentTable.Columns[i].IsKey)
                {
                    str += "[" + CurrentTable.Columns[i].Name + "]=a.[" + CurrentTable.Columns[i].Name + "],";
                }
            }
            str = str.Trim(',');
            sb.AppendLine(str);
            sb.AppendLine("     from " + TableName + ",EMBASE.embase2.dbo." + TableName + " a where ");
            str = "     ";
            for (int i = 0; i < CurrentTable.Columns.Count; i++)
            {
                if (CurrentTable.Columns[i].IsKey)
                {
                    str +="     "+ TableName + ".[" + CurrentTable.Columns[i].Name + "]=a.[" + CurrentTable.Columns[i].Name + "] AND";
                }
            }
            str = str.Substring(0, str.Length - 3);
            sb.AppendLine(str);
            sb.AppendLine("     and (a.RecTime>=@maxTime OR a.ModTime>=@maxTime)");
            sb.AppendLine("delete from " + TableName + "  where not exists(select 1 from EMBASE.embase2.dbo." + TableName + " as a where ");
            str="";
            for (int i = 0; i < CurrentTable.Columns.Count; i++)
            {
                if (CurrentTable.Columns[i].IsKey)
                {
                    str+=" a.["+CurrentTable.Columns[i].Name+"]="+TableName+".["+CurrentTable.Columns[i].Name+"] and";
                }
            }
            str=str.Substring(0,str.Length-3);
            sb.Append(str+")");
            sb.AppendLine();
            sb.AppendLine("end");
            return sb.ToString() ;
        }
    }
}